package edu.polsl.bio.str.manager.dao;

import edu.polsl.bio.str.common.entity.DotPlot;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.jdbc.Work;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * //todo class description
 * <p/>
 * Created on: Jun 17, 2011 4:30:56 PM <br/>
 * <a href="http://www.polsl.pl">www.polsl.pl</a>
 *
 * @author lukasz.olczak@polsl.pl
 */
public class DotPlotDaoImpl extends HibernateDaoSupport implements DotPlotDao {
  /**
   * slf4j Logger.
   */
  private static final Logger log = LoggerFactory.getLogger(DotPlotDaoImpl.class.getName());

  private static final String GET_SQL = "SELECT * FROM dot_plots d force index (dots_select_index) " +
                                        "where ref_id=:refId and query_id=:queryId and " +
                                        "ref_chromosome_id=:refChromId and query_chromosome_id=:queryChromId " +
                                        "LIMIT :firstResult,:maxResults";

  @Override
  public DotPlot create(DotPlot entity) {
    Serializable dotPlotId = getHibernateTemplate().save(entity);

    return (DotPlot) getHibernateTemplate().get(DotPlot.class, dotPlotId);
  }

  public void bulkInsert(final List<DotPlot> dotPlots) {

    getHibernateTemplate().getSessionFactory().getCurrentSession().doWork(new Work() {
      @Override
      public void execute(Connection connection) throws SQLException {
        log.debug("Starting bulk load of dots");
        Statement st = connection.createStatement();
        for (DotPlot row : dotPlots) {

          StringBuilder sb = new StringBuilder();
          sb.append("INSERT INTO dot_plots(ref_id,query_id,ref_chromosome_id,query_chromosome_id,motif,motif_length,");
          sb.append("ref_start,ref_end,ref_no_repeats,query_start,query_end,query_no_repeats,ref_str_id,query_str_id,");
          sb.append("query_strand) values (");
          sb.append(row.getRefId());
          sb.append(",");
          sb.append(row.getQueryId());
          sb.append(",");
          sb.append(row.getRefChromosomeId());
          sb.append(",");
          sb.append(row.getQueryChromosomeId());
          sb.append(",'");
          sb.append(row.getMotif());
          sb.append("',");
          sb.append(row.getMotifLength());
          sb.append(",");
          sb.append(row.getRefStart());
          sb.append(",");
          sb.append(row.getRefEnd());
          sb.append(",");
          sb.append(row.getRefNoRepeats());
          sb.append(",");
          sb.append(row.getQueryStart());
          sb.append(",");
          sb.append(row.getQueryEnd());
          sb.append(",");
          sb.append(row.getQueryNoRepeats());
          sb.append(",");
          sb.append(row.getRefStrId());
          sb.append(",");
          sb.append(row.getQueryStrId());
          sb.append(",'");
          sb.append(row.getQueryStrand().name());
          sb.append("')");

          st.addBatch(sb.toString());
        }
        int rowsInserted = 0;
        int[] results = st.executeBatch();
        for (int count : results) {
          rowsInserted += count;
        }
        if (rowsInserted != dotPlots.size()) {
          throw new RuntimeException(rowsInserted + " rows inserted from " + dotPlots.size());
        }
        st.close();
        log.debug("Bulk load of dots finished ({} rows inserted)", rowsInserted);
      }
    });
  }

  @Override
  public List<DotPlot> getDotPlots(Long refId, Long queryId, Long refChromosomeId, Long queryChromosomeId) {

    DetachedCriteria query = DetachedCriteria.forClass(DotPlot.class)
        .add(Restrictions.eq("refId", refId)).add(Restrictions.eq("queryId", queryId))
        .add(Restrictions.eq("refChromosomeId", refChromosomeId)).add(Restrictions.eq("queryChromosomeId", queryChromosomeId));
    return getHibernateTemplate().findByCriteria(query);
  }

  @Override
  @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_UNCOMMITTED)
  public List<DotPlot> getDotPlots(Long refId, Long queryId, Long refChromosomeId, Long queryChromosomeId, int firstResult, int count) {
    SQLQuery query = getHibernateTemplate().getSessionFactory().getCurrentSession()
        .createSQLQuery(GET_SQL).addEntity(DotPlot.class);
    query.setParameter("refId", refId).setParameter("queryId", queryId).setParameter("refChromId", refChromosomeId)
        .setParameter("queryChromId", queryChromosomeId).setParameter("firstResult", firstResult)
        .setParameter("maxResults", count);
    List<DotPlot> results = query.list();
//    for (DotPlot entity : results) {
//      getHibernateTemplate().evict(entity);
//    }
    return results;
  }
}
